package sel.me.sharding.learning;

import com.alibaba.druid.pool.DruidDataSource;

import io.shardingsphere.api.config.rule.ShardingRuleConfiguration;
import io.shardingsphere.api.config.rule.TableRuleConfiguration;
import io.shardingsphere.api.config.strategy.InlineShardingStrategyConfiguration;
import io.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import io.shardingsphere.shardingjdbc.jdbc.core.connection.ShardingConnection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.ConcurrentHashMap;
import javax.sql.DataSource;

public class ShardingJDBCLearning {

	public static void main(String[] args) throws SQLException {
		ShardingJDBCLearning shardingJDBCLearning = new ShardingJDBCLearning();

		DataSource shardingDataSource = shardingJDBCLearning.getShardingDataSource();

//		shardingJDBCLearning.testSelect(shardingDataSource);
//		shardingJDBCLearning.testInsertOrder(shardingDataSource);
//		shardingJDBCLearning.testInsertUser(shardingDataSource);
		shardingJDBCLearning.testJoin(shardingDataSource);


//		TransactionSynchronizationManager ts;
		ShardingConnection sc;
//		sc.commit();
	}

	public void testSelect(DataSource shardingDataSource) throws SQLException{
		Connection c = shardingDataSource.getConnection();
		PreparedStatement ps = c.prepareStatement("select * from t_order where order_id = ? and user_id = ?");
		ps.setInt(1, 1);
		ps.setInt(2, 1);
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			String orderId = rs.getString("order_id");
			System.out.println(orderId);
		}
	}

	public void testJoin(DataSource shardingDataSource) throws SQLException{
		Connection c = shardingDataSource.getConnection();
//		PreparedStatement ps = c.prepareStatement("select * from t_user tu left join t_order torder on tu.user_id = torder.user_id where torder.order_id=1");
		PreparedStatement ps = c.prepareStatement("select * from t_user tu left join t_order torder on tu.user_id = torder.user_id where tu.user_id=1");
		ResultSet rs = ps.executeQuery();

		while(rs.next()){
			String userId = rs.getString("user_id");
			String orderId = rs.getString("order_id");
			System.out.println("userId: " +userId +"-orderId: " + orderId);
		}

	}


	public void testInsertOrder(DataSource shardingDataSource) throws SQLException{
		Connection c = shardingDataSource.getConnection();
		PreparedStatement ps = c.prepareStatement("insert into t_order(user_id, order_id) values(?,?)");
		ps.setInt(1, 1);
		ps.setInt(2, 1);
		boolean rs = ps.execute();
	}

	public void testInsertUser(DataSource shardingDataSource) throws SQLException{
		Connection c = shardingDataSource.getConnection();
		PreparedStatement ps = c.prepareStatement("insert into t_user(user_id) values(?)");
		ps.setInt(1, 1);
		boolean rs = ps.execute();
	}


	public DataSource getShardingDataSource() throws SQLException {
		Map<String, DataSource> dataSourceMap = createDataSourceMap();

	    // 配置Order表规则
	    TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();
	    orderTableRuleConfig.setLogicTable("t_order");
	    orderTableRuleConfig.setActualDataNodes("ds${0..1}.t_order_${0..1}");
		// 配置分库 + 分表策略
		orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "ds${user_id % 2}"));
		orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order_${order_id % 2}"));

		// 配置User表规则
		TableRuleConfiguration userTableRuleConfig = new TableRuleConfiguration();
		userTableRuleConfig.setLogicTable("t_user");
		userTableRuleConfig.setActualDataNodes("ds${0..1}.t_user_${0..1}");
		// 配置分库 + 分表策略
		userTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "ds${user_id % 2}"));
		userTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "t_user_${user_id % 2}"));


	    // 配置分片规则
	    ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
	    shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);
		shardingRuleConfig.getTableRuleConfigs().add(userTableRuleConfig);
//        shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());
//        shardingRuleConfig.getTableRuleConfigs().add(getOrderItemTableRuleConfiguration());
//        shardingRuleConfig.getBindingTableGroups().add("t_order, t_order_item");
//        shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", new DatabaseShardingAlgorithm()));
//        shardingRuleConfig.setDefaultTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("order_id", new TablePreciseShardingAlgorithm(), new TableRangeShardingAlgorithm()));

	    Properties properties = new Properties();
        properties.setProperty("sql.show", Boolean.TRUE.toString());
	    
	    // 获取数据源对象

		return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new ConcurrentHashMap<String, Object>(), properties);
	}
	
	


     private Map<String, DataSource> createDataSourceMap() {
    	 // 配置真实数据源
 	    Map<String, DataSource> dataSourceMap = new HashMap<>();
 	    
 	    // 配置第一个数据源
 	    DruidDataSource dataSource1 = new DruidDataSource();
 	    dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
 	    dataSource1.setUrl("jdbc:mysql://localhost:3306/ds0?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&serverTimezone=UTC");
 	    dataSource1.setUsername("root");
 	    dataSource1.setPassword("root");
 	    dataSourceMap.put("ds0", dataSource1);
 	    
 	    // 配置第二个数据源
 	    DruidDataSource  dataSource2 = new DruidDataSource();
 	    dataSource2.setDriverClassName("com.mysql.jdbc.Driver");
 	    dataSource2.setUrl("jdbc:mysql://localhost:3306/ds1?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&serverTimezone=UTC");
 	    dataSource2.setUsername("root");
 	    dataSource2.setPassword("root");
 	    dataSourceMap.put("ds1", dataSource2);
		return dataSourceMap;
	}



	TableRuleConfiguration getOrderTableRuleConfiguration() {
         TableRuleConfiguration result = new TableRuleConfiguration();
         result.setLogicTable("t_order");
         result.setActualDataNodes("ds${0..1}.t_order${0..1}");
         result.setKeyGeneratorColumnName("order_id");
         return result;
     }
     
     TableRuleConfiguration getOrderItemTableRuleConfiguration() {
         TableRuleConfiguration result = new TableRuleConfiguration();
         result.setLogicTable("t_order_item");
         result.setActualDataNodes("ds${0..1}.t_order_item${0..1}");
         return result;
     }
     
     
}
